import json
import time

from rest_framework import viewsets, status
from rest_framework.response import Response
from rest_framework.exceptions import AuthenticationFailed
# from rest_framework.pagination import PageNumberPagination
from django.db.models import Q

from .models import ZtProject, ZtModule, ZtStory, ZtTask, ZtCase, ZtBug,ZtProjectProduct,ZtTestRun
from .serializers import ZtProjectSerializer, ZtModuleSerializer, ZtProjectDetailSerializer

import traceback
# # 创建自定义分页类
# class CustomPageNumberPagination(PageNumberPagination):
#     page_size = 10  # 默认每页显示10条数据
#     page_size_query_param = 'page_size'  # 允许客户端通过查询参数 page_size 来指定每页大小
#     max_page_size = 100  # 限制客户端通过 page_size 查询参数设置的最大页大小

import time
from functools import wraps

def timing_decorator(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"Function {func.__name__!r} executed in {(end_time - start_time)*1000:.2f}ms")
        return result
    return wrapper

#判断是否登录
# class TokenAuthPermission(BasePermission):
def has_permission(request):

    # response = Response({'code': 0,  'message': '未登录'}, status=status.HTTP_401_UNAUTHORIZED)

    # print(request.session.get('token'), request.META.get('HTTP_TOKEN'))

    # session没有token，则未登录
    session_token = request.session.get('token')
    if not session_token:
        # print('没有session')
        return False

    # 如果未传入token，则未登录
    request_token = request.META.get('HTTP_TOKEN')
    if not request_token:
        # print('没有token')
        return False

    #如果都传入，则比对是否一致
    if session_token == request_token:
        return True
    else:
        return False

#查询一个产品是否对应多个项目，如果是需要筛选模块（一个项目对应多个产品，默认这个项目包含所有产品的模块）
# @timing_decorator
def if_select_module(project_id):
    #查询所有项目
    projects = ZtProject.objects.filter(project=0, deleted='0').values('id').distinct()
    projects = [project['id'] for project in projects]

    #查询所有项目与产品的对应表
    product_projectSet_dict = {}  # 产品对应的项目字典
    project_productNum = set()  #项目对应的产品集合

    project_products = ZtProjectProduct.objects.all().values('project', 'product').distinct()

    #计算指定项目对应的产品的项目数量
    for project_product in project_products:
        product = project_product['product']
        project = project_product['project']

        # 项目对应的产品集合
        if project == project_id:
            project_productNum.add(product)

        #产品对应的项目集合
        if product not in product_projectSet_dict.keys():
            product_projectSet_dict[product] = set()
        if project in projects:
            product_projectSet_dict[product].add(project)

    #如果项目没有对应的产品（实际不可能存在）或者没有该项目
    if len(project_productNum) == 0:
        return False
    elif len(project_productNum) == 1:
        product_projectSet = product_projectSet_dict[list(project_productNum)[0]]
        #如果产品只有一个项目，也不需要筛选
        if len(product_projectSet) == 1:
            return False
        else:
            return True
    else:
        #如果一个项目对应多个产品，不筛选模块
        return False


# import time
# from functools import wraps
#
# def timing_decorator(func):
#     @wraps(func)
#     def wrapper(*args, **kwargs):
#         start_time = time.time()
#         result = func(*args, **kwargs)
#         end_time = time.time()
#         print(f"Function {func.__name__!r} executed in {(end_time - start_time)*1000:.2f}ms")
#         return result
#     return wrapper

#获取所有项目
class ZtProjectViewSet(viewsets.ViewSet):

    def initial(self, request, *args, **kwargs):
        super(ZtProjectViewSet, self).initial(request, *args, **kwargs)
        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            raise AuthenticationFailed({'code': 0, 'msg': '未登录'})

    # 格式化数据
    @timing_decorator
    def list(self, request, *args, **kwargs):
        '''
        返回所有项目即project为0的条目，其中type为project为项目，program为项目集
        '''
        try:

            #所有项目和项目集
            projects = ZtProject.objects.filter(deleted='0',project=0).select_related('PM').prefetch_related('team_project', 'team_project__account').distinct()

            #将list转为dict
            project_dict = {}
            for project in projects:
                project_dict[project.id] = project

            #先将项目集的PO、PM、QD、RD合并到members，再将项目以及对应的执行的PO、PM、QD、RD和对应的团队合并到members
            for _id, project in project_dict.items():

                #排除看板
                # if project.type == 'kanban':
                #     continue

                #该条信息本身的项目成员
                members = set()

                #先添加负责人
                for member in [project.PO,project.QD,project.RD]:
                    if member:
                        members.add(member)

                if project.PM:
                    members.add(project.PM.account)

                #添加项目团队的人
                team = project.team_project.all()
                if team:
                    for member in team:
                        if member.account:
                            members.add(member.account.account)

                #如果project不为0，则代表他为项目下的执行迭代，将成员挂载到对应的项目上
                if project.project == 0:
                    project_dict[_id].members = members
                else:
                    project_dict[project.project].members = members

            #筛选project为0即项目和项目集
            project_list = [project for project in project_dict.values() if project.project==0]

            #排序，将program放前面
            project_list = sorted(project_list, key=lambda x:x.type)

            #整理成树结构
            project_tree_select = self.module_to_tree(project_list)

            # 根据登录人进行筛选，如果是admin，则不筛选
            # 登录用户名
            account = self.request.session['account']

            # with open('1.json', 'w') as f:
            #     json.dump(project_tree_select,f, ensure_ascii=False)
            # print(project_tree_select)

            if account != 'admin':
                project_tree_select = self.get_projects_by_select(project_tree_select,account)

            #将树结构中child有dict转为list,并拼接members
            project_tree_select = self.update_child_type(project_tree_select)

            return Response({'code': 1, 'msg': '获取成功', 'data': list(project_tree_select.values())})
        except:
            print(traceback.format_exc())
            return Response({'code': 0, 'msg': '获取失败'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    # 将路径整理成树结构的函数
    def module_to_tree(self, projects):

        # 初始化树结构
        tree = {}

        # 遍历路径列表，构建树结构
        for project in projects:

            # 移除路径两端的逗号，并分割成步骤列表
            steps = project.path.strip(',').split(',')

            # 递归地构建或更新树结构
            current_level = tree
            for step in steps:
                if step:  # 确保步骤不为空
                    if step not in current_level:
                        current_level[step] = {'id': project.id,
                                               'name': project.name,
                                               'members': project.members,
                                               'type': project.type,
                                               'child': {}}

                    current_level = current_level[step]['child']
        return tree

    # 将树结构中child有dict转为list,并拼接members
    def update_child_type(self, tree):

        # 更新状态和日期
        def update_parent_status(node):
            # 获取子节点字典
            children = node.get('child', {})

            # 如果没有子节点，则不更新状态
            if not children:
                # 删除members
                del node['members']

                # 将字段转为列表
                node['child'] = list(node['child'].values())
                return

            # 遍历所有子节点
            for child_node in children.values():
                # 递归更新子节点的状态
                update_parent_status(child_node)

            # 将字段转为列表
            node['child'] = list(node['child'].values())
            # 删除members
            del node['members']

        # 更新状态和日期
        for _, module in tree.items():
            update_parent_status(module)

        return tree

    # 根据登录人进行筛选进行筛选
    def get_projects_by_select(self, tree, account):

        # 递归函数，用于遍历树并找到所有匹配name的模块及其所有子节点
        def traverse_and_filter(node):
            # 如果当前节点匹配name，则保留该节点及其所有子节点
            if account is not None and account in node['members']:
                # 复制当前节点，并递归处理子节点
                filtered_node = node.copy()
                if 'child' in node:
                    filtered_node['child'] = {k: traverse_and_filter(v) for k, v in node['child'].items() if
                                              traverse_and_filter(v) is not None}
                return filtered_node

            # 如果当前节点不匹配，继续遍历子节点
            filtered_children = {}
            for child_id, child_node in node.get('child', {}).items():
                child_result = traverse_and_filter(child_node)
                if child_result:
                    filtered_children[child_id] = child_result

            # 如果子节点中有匹配的，则保留当前节点，包含所有匹配的子节点
            if filtered_children:
                filtered_node = node.copy()
                filtered_node['child'] = filtered_children
                return filtered_node

            # 如果没有匹配的子节点，返回None
            return None

        # 遍历整棵树，应用筛选条件
        filtered_tree = {}
        for mod_id, mod_node in tree.items():
            result = traverse_and_filter(mod_node)
            if result:
                filtered_tree[mod_id] = result

        return filtered_tree

#获取指定项目的项目信息和对应的版本
class ZtProjectDetailViewSet(viewsets.ModelViewSet):
    queryset = ZtProject.objects
    serializer_class = ZtProjectDetailSerializer

    def initial(self, request, *args, **kwargs):
        super(ZtProjectDetailViewSet, self).initial(request, *args, **kwargs)
        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            raise AuthenticationFailed({'code': 0, 'msg': '未登录'})

    # 格式化数据
    @timing_decorator
    def list(self, request, *args, **kwargs):
        try:

            # 获取 project_id 参数
            project_id = kwargs.get('project_id')

            # 如果project_id没有传
            if not project_id:
                return Response({'code': 0, 'msg': '没有携带项目id'}, status=status.HTTP_400_BAD_REQUEST)

            #查询条件
            query = Q()
            query &= Q(id=project_id)  # 根据项目id查询需求
            query &= Q(project='0')  # 项目不能被删除
            or_query = query | Q(project=project_id) #对应的执行版本
            query = or_query & Q(deleted='0')  # 需求不能被删除

            # 使用 project_id 进行查询
            queryset = self.queryset.filter(query).distinct()

            # 未查询到
            if not queryset.exists():
                return Response({'code': 0, 'msg': '该项目id不存在'})

            serializer = self.get_serializer(queryset, many=True)

            executions = []
            # 获取产品和执行id
            for data in serializer.data:
                if data['project'] == 0:
                    project = data
                    project['type'] = project['hasProduct']
                    del project['hasProduct']
                    del project['project']

                    #项目的资金转换为万元
                    if project['budget'] and project['budget'].isdigit():
                        project['budget'] = int(project['budget'])/10000 if project['budget'] != '0' else 0
                        project['budget'] = '{}万元'.format(project['budget'])
                    else:
                        project['budget'] = '待定'

                    #判断登录用户是否为项目负责人
                    login_account = self.request.session['account']
                    if login_account == project['PM']:
                        project['if_PM'] = True
                    else:
                        project['if_PM'] = False

                else:
                    #执行版本
                    executions.append({'value':data['id'], 'label':"#{} {}".format(data['id'], data['name'])})

            return Response({'code': 1, 'msg': '获取成功', 'data': {'detail': project, 'executions':executions}})
        except:
            print(traceback.format_exc())
            return Response({'code': 0, 'msg': '获取失败'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


#统计需求各个状态数量
class ZtStatsViewSet(viewsets.ViewSet):
    # queryset = ZtStory.objects
    # serializer_class = ZtStorySerializer
    #定义所有需求数
    # total = 0
    # stories = set()

    def initial(self, request, *args, **kwargs):
        super(ZtStatsViewSet, self).initial(request, *args, **kwargs)
        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            raise AuthenticationFailed({'code': 0, 'msg': '未登录'})

        #定义所有需求数
        self.total = 0
        self.stories = set()
        self.hasProduct = 0

    @timing_decorator
    def list(self, request):
        '''
        根据项目id只筛选非父节点的需求，即父节点为-1的需求,并统计各个状态的数量
        '''
        project_id = request.query_params.get('project_id', None)

        # 如果project_id没有传
        if not project_id:
            return Response({'code': 0, 'msg': '没有携带项目id'}, status=status.HTTP_400_BAD_REQUEST)

        #如果该项目对应的产品有多个项目，则需要筛选模块
        self.if_select_module = if_select_module(int(project_id))
        # res = {
        #     "total":0,
        #     "story": None,
        #     "design": None,
        #     "develop": None,
        #     "test": None,
        #     "implement": None,
        #     "acceptance": None
        # }

        #需求设计验收统计
        res, released_stories = self.story_design_acceptance_stats(project_id)

        #开发统计
        develop_implement_res, modules, chart_module = self.develop_stats(project_id)

        #测试
        _, done_tests, test_stories = self.test_stats(project_id)

        test_res = self.test_modify_stats(project_id)

        #实施
        # implement_res  = self.implement_stats(released_stories, done_tests, test_stories)

        #字典合并
        res_data = {**res, **develop_implement_res, **test_res}

        res = {
            'code': 1,
            "msg": "获取成功",
            "data": {'stats':res_data,'modules':list(modules.values()), 'chart':{'module':chart_module}}
        }

        return Response(res)
        # return self.queryset.all()

    #获取该项目所有以及模块以及子模块和以及模块对应表
    # @timing_decorator
    def first_level_module(self, project_id):
        '''
        获取该项目所有以及模块以及子模块和以及模块对应表
        '''
        # 搜索条件
        # 初始化一个空的查询集
        root_ids = set()
        # 查询项目及对应的迭代（迭代也可以建模块）
        query_project = Q(id=project_id) | Q(project=project_id)
        query_project = Q(deleted='0') & query_project
        project_excutions = ZtProject.objects.filter(query_project).values('id').distinct()
        for project_excution in project_excutions:
            root_ids.add(project_excution['id'])

        # 查询项目对应的产品
        project_products = ZtProjectProduct.objects.filter(project__in=root_ids).values('product').distinct()
        for project_product in project_products:
            root_ids.add(project_product['product'])

        # 获取该项目所有模块
        query = Q()
        # query_module &= Q(root__projectProduct_product__project_id=project_id)  # 根据项目id查询模块
        # query_module &= Q(root__projectProduct_product__project__deleted='0')  # 项目不能被删除
        # 使用括号来确保“或”条件与之前的“与”条件组合
        query &= Q(root__in=root_ids)
        query &= Q(deleted='0')  # 模块不能被删除

        #先查询项目对应产品的所有模块
        modules = ZtModule.objects.filter(query).values('id', 'name', 'path', 'parent').distinct()

        # 处理模块将,151,152,改为,738,151,152,
        # 获取所有的位尾节点模块
        model_path_end = {}
        for module in modules:
            path = module['path'].strip(',').split(',')
            if len(path) > 1:
                model_path_end[path[-1]] = {'id': module['id'], 'path': module['path']}

        # 处理首节点对应尾巴节点的情况
        for i, module in enumerate(modules):
            path = module['path'].strip(',').split(',')
            if len(path) > 1 and path[0] in model_path_end.keys():
                pre_path = model_path_end[path[0]]
                path = pre_path['path'] + ''.join(path[1:]) + ','
                module['path'] = path

        # 排序，将父模块放到前面
        modules = sorted(modules, key=lambda x: len(x['path'].split(',')))

        if self.if_select_module:

            project_modules = set()

            #查询项目对应需求的所有模块
            query = Q()
            query &= Q(projectStory_story__project__id=project_id)  # 根据项目id查询需求
            query &= Q(projectStory_story__project__deleted='0')  # 项目不能被删除
            query &= Q(deleted='0')  # 需求不能被删除
            query &= Q(type='story')  # 确保为研发需求

            # stories = ZtStory.objects.filter(query).exclude(status='closed').select_related('module').distinct()
            stories = ZtStory.objects.filter(query).select_related('module').distinct()

            for story in stories:
                module = story.module
                if module:
                    project_modules.add(int(module.path.strip(',').split(',')[0]))

            # 查询项目对应开发任务的所有模块
            query = Q()
            query &= Q(project__id=project_id)  # 根据项目id查询需求
            query &= Q(project__deleted='0')  # 项目不能被删除
            query &= Q(deleted='0')  # 任务不能被删除

            develops = ZtTask.objects.filter(query).select_related('module').distinct()

            for develop in develops:
                module = develop.module
                if module:
                    project_modules.add(int(module.path.strip(',').split(',')[0]))

            # 查询项目对应测试bug的所有模块
            query = Q()
            query &= Q(project__id=project_id)  # 根据项目id查询需求
            query &= Q(project__deleted='0')  # 项目不能被删除
            query &= Q(deleted='0')  # 需求不能被删除

            bugs = ZtBug.objects.filter(query).select_related('module').distinct()

            for bug in bugs:
                module = bug.module
                if module:
                    project_modules.add(int(module.path.strip(',').split(',')[0]))

            # 查询项目对应测试用例的所有模块
            query = Q()
            # 项目关联有问题，暂且用项目的所有执行关联
            query &= Q(execution__project=project_id)  # 根据项目id查询需求
            query &= Q(deleted='0')  # 用例不能被删除

            cases = ZtCase.objects.filter(query).select_related('module').distinct()

            for case in cases:
                module = case.module
                if module:
                    project_modules.add(int(module.path.strip(',').split(',')[0]))

        #记录一级模块及对应关系
        res = {}
        child_parent_map = {}

        if not self.if_select_module:
            for module in modules:
                #为0则为一级模块
                if module['parent'] == 0:
                    if module['id'] not in res:
                        res[module['id']] = {'id':module['id'], 'name':module['name'], 'progress':0, 'doing_stories':set(), 'all_stories':set()}

                child_parent_map[module['id']] = int(module['path'].strip(',').split(',')[0])
        else:
            for module in modules:
                # 为0则为一级模块
                if module['parent'] == 0 and module['id'] in project_modules:
                    if module['id'] not in res:
                        res[module['id']] = {'id': module['id'], 'name': module['name'], 'progress': 0,
                                             'doing_stories': set(), 'all_stories': set()}

                child_parent_map[module['id']] = int(module['path'].strip(',').split(',')[0])


        return res, child_parent_map

    def story_design_acceptance_stats(self, project_id):
        '''
        根据项目id只筛选非父节点的需求，即父节点为-1的需求,并统计各个状态的数量
        '''

        # 定义各个数量
        story_doing = 0  # 进行中
        story_done = 0  # 已完成
        design_doing = 0
        design_done = 0
        acceptance_done = 0
        # wait = 0    #未开始

        # 已发布的需求
        released_stories = set()

        try:

            if project_id:

                # 搜索条件
                # 初始化一个空的查询集
                query = Q()
                query &= Q(projectStory_story__project__id=project_id)  # 根据项目id查询需求
                query &= Q(projectStory_story__project__deleted='0')  # 项目不能被删除
                query &= Q(deleted='0')  # 需求不能被删除
                query &= Q(type='story')  # 确保为研发需求

                # filter_stories = ZtStory.objects.filter(query).exclude(Q(parent=-1) | Q(status='closed'))\
                #                                 .values('id','status','addStage','stage').distinct()
                filter_stories = ZtStory.objects.filter(query).exclude(parent=-1) \
                    .values('id', 'status', 'addStage', 'stage').distinct()

                for story in filter_stories:

                    if story['status'] == 'active':
                        #如果评审通过，则需求和设计都完成
                        design_done += 1
                        story_done += 1

                    elif story['addStage'] == 'design':
                        #如果评审不通过，但是阶段为设计，则设计进行中、需求完成
                        story_done += 1
                        design_doing += 1
                    elif story['addStage'] == 'story':
                        #如果评审不通过，但是阶段为需求，则需求进行中
                        story_doing += 1

                    if story['stage'] == 'released':
                        acceptance_done += 1
                        released_stories.add(story['id'])

                    #总需求数
                    self.total += 1
                    self.stories.add(story['id'])
        except:
            # print(traceback.format_exc())
            pass
        finally:
            res = {
                # "total": self.total,
                "story": {
                    'total': self.total,
                    'doing': story_doing,
                    'done': story_done,
                    'wait': self.total - story_doing - story_done
                },
                "design": {
                    'total': self.total,
                    'doing': design_doing,
                    'done': design_done,
                    'wait': self.total - design_doing - design_done
                },
                "acceptance": {
                    'total': self.total,
                    'doing': 0,
                    'done': acceptance_done,
                    'wait': self.total - acceptance_done
                }
            }

        return res, released_stories

    def develop_stats(self, project_id):
        '''
        根据项目id筛选开发任务,并统计各个状态的数量，以开发完成情况当作一级模块进度
        '''

        #模块获取
        modules, child_parent_map = self.first_level_module(project_id)

        # 功能完成情况统计
        chart_module = {
            "doing": 0,
            "done": 0,
            "wait": 0
        }

        #设计的需求
        stories = set()
        doing = 0  # 进行中
        done = 0  # 已完成

        #开发任务和实施任务状态
        develop_wait = 0
        develop_doing = 0
        develop_done = 0

        implement_wait = 0
        implement_doing = 0
        implement_done = 0

        doing_story = set()

        try:
            if project_id:
                # 首先通过project筛选，然后找出所有关联需求的任务，然后如果状态不为完成，
                # 则为进行中，拿总开发数-进行中=完成开发数,'wait','doing','done','pause','closed'
                filter_devs = ZtTask.objects.filter(project=project_id, project__deleted='0',
                                                    deleted='0').exclude(status='cancel').values('id','status','story', 'module','type').distinct()

                for dev in filter_devs:

                    #统计
                    if dev['status'] == 'wait':
                        if dev['type'] == 'implement':
                            implement_wait += 1
                        else:
                            develop_wait += 1
                    elif dev['status'] in ['doing', 'pause']:
                        if dev['type'] == 'implement':
                            implement_doing += 1
                        else:
                            develop_doing += 1
                    elif dev['status'] in ['done', 'closed']:
                        if dev['type'] == 'implement':
                            implement_done += 1
                        else:
                            develop_done += 1

                    # 模块对应的需求
                    if dev['status'] != 'done' and dev['status'] != 'closed':

                        if dev['story'] not in doing_story:
                            doing += 1
                            doing_story.add(dev['story'])

                            #为对应模块添加进行中的需求
                            if dev['module'] != 0 and dev['module'] in child_parent_map.keys():
                                modules[child_parent_map[dev['module']]]['doing_stories'].add(dev['story'])

                    stories.add(dev['story'])
                    #对应模块所有需求，模块为0则不统计
                    if dev['module'] != 0  and dev['module'] in child_parent_map.keys():
                        modules[child_parent_map[dev['module']]]['all_stories'].add(dev['story'])

                #计算模块进度以及一级模块整体情况
                for key, value in modules.items():
                    all_stories_num = len(modules[key]['all_stories'])
                    doing_stories_num = len(modules[key]['doing_stories'])

                    progress = (all_stories_num - doing_stories_num) / all_stories_num if all_stories_num != 0 else 0

                    #采用四舍五入，如果没有100但是四舍五入为100，则限制为99，0则为1
                    progress_approximate = int(progress*100+0.5)    #四舍五入
                    if progress_approximate == 100 and progress!= 1:
                        progress_approximate = 99
                    elif progress_approximate == 0 and progress!= 0:
                        progress_approximate = 1

                    modules[key]['progress'] = progress_approximate

                    #功能完成情况统计
                    if progress_approximate == 0:
                        chart_module['wait'] += 1
                    elif progress_approximate == 100:
                        chart_module['done'] += 1
                    else:
                        chart_module['doing'] += 1

                    #删除需求键值对
                    del modules[key]['all_stories']
                    del modules[key]['doing_stories']
        except:
            print(traceback.format_exc())
            pass
        finally:
            res = {
                "develop": {
                    'total': develop_wait+develop_doing+develop_done, #总需求+设计的需求（并集）
                    'doing': develop_doing,
                    'done': develop_done,
                    'wait': develop_wait   #用总需求-这里涉及到的需求（差集）
                },
                "implement": {
                    'total': implement_wait + implement_doing + implement_done,  # 总需求+设计的需求（并集）
                    'doing': implement_doing,
                    'done': implement_done,
                    'wait': implement_wait  # 用总需求-这里涉及到的需求（差集）
                }
            }

        return res, modules, chart_module

    def test_stats(self, project_id, only_done=False):
        '''
        根据项目id筛选对应测试单对应的用例以及bug,并统计各个状态的数量
        若用例/bug/用例bug通过则为完成
        其余均为未完成，拿总测试数-完成测试数=未完成测试数
        '''

        doing = 0  # 进行中
        done = 0  # 已完成

        #已完成的测试需求
        done_tests = set()
        test_stories = set()

        story_case = {}
        story_num = 0

        try:

            if project_id:

                # 搜索条件
                # 初始化一个空的查询集
                query = Q()
                query &= Q(testRun_case__task__project__id=project_id)  # 根据项目id查询需求
                query &= Q(testRun_case__task__project__deleted='0')  # 项目不能被删除
                query &= Q(testRun_case__task__deleted='0')  # 测试单不能被删除
                query &= Q(deleted='0')  # 需求不能被删除

                # 首先通过project筛选，然后找出对应测试单对应的用例
                filter_cases = ZtCase.objects.filter(query).exclude(story=0)\
                    .values('id', 'story', 'testRun_case__lastRunResult').distinct()
                for case in filter_cases:
                    #默认为通过，如果有不通过的案例则改为不通过
                    if case['story'] not in story_case.keys():
                        story_case[case['story']] = 'pass'
                        story_num += 1

                    if case['testRun_case__lastRunResult'] != 'pass':
                        story_case[case['story']] = 'not_pass'

                    test_stories.add(case['story'])

                #通过project筛选，然后找出对应的bug
                filter_bugs = ZtBug.objects.filter(project__id=project_id,project__deleted='0',
                                                   deleted='0').exclude(story=0).values('id','story','status').distinct()
                for bug in filter_bugs:

                    test_stories.add(bug['story'])
                    #如果对应的需求存在用例，那查看用例是否通过，通过则查看bug是否被解决，解决则为通过
                    # bug的状态不为激活就是解决
                    if bug['story'] in story_case.keys():

                        if bug['status'] != 'active' and story_case[bug['story']] == 'pass':
                            done += 1
                            done_tests.add(bug['story'])

                        # 删除该需求
                        del story_case[bug['story']]

                    else:
                        story_num += 1
                        if bug['status'] != 'active':
                            done += 1
                            done_tests.add(bug['story'])

                #在判断如果需求只有用例的情况
                for key, value in story_case.items():
                    if value == 'pass':
                        done += 1
                        done_tests.add(key)

                doing = story_num - done

        except:
            pass
        finally:
            if only_done:
                return done_tests
            else:
                res = {
                    "test": {
                        'total': len(self.stories | test_stories),    #总需求+设计的需求（并集）
                        'doing': doing,
                        'done': done,
                        'wait': len(self.stories - test_stories)      #用总需求-这里涉及到的需求（差集）
                    }
                }

                return res, done_tests, test_stories

    def test_modify_stats(self, project_id):
        '''
        根据项目id筛选对应测试单对应的用例以及bug,并统计各个状态的数量
        若用例/bug/用例bug通过则为完成,用例失败为进行中，未执行为未开始
        其余均为未完成，拿总测试数-完成测试数=未完成测试数
        '''

        wait = 0    #未开始
        doing = 0  # 进行中
        done = 0  # 已完成

        #已完成的测试需求
        done_tests = set()
        test_stories = set()

        story_case = {}
        story_num = 0

        try:

            if project_id:

                # 搜索条件
                # 初始化一个空的查询集
                query = Q()
                query &= Q(task__project_id=project_id)  # 根据项目id查询需求
                query &= Q(task__project__deleted='0')  # 项目不能被删除
                query &= Q(task__deleted='0')  # 测试单不能被删除

                # 首先通过project筛选，然后找出对应测试单对应的用例
                filter_cases = ZtTestRun.objects.filter(query).distinct()
                for case in filter_cases:
                    if case.lastRunResult:
                        #如果为pass则为完成
                        if case.lastRunResult == 'pass':
                            done += 1
                        else:
                            doing += 1
                    else:
                        #如果没有值
                        wait += 1

                #通过project筛选，然后找出对应的bug
                filter_bugs = ZtBug.objects.filter(project__id=project_id,project__deleted='0',
                                                   deleted='0').distinct()
                for bug in filter_bugs:

                    #如果status不为active，则为完成
                    if bug.status != 'active':
                        done += 1
                    else:
                        #如果confirmed为1，则为确认，则为进行中
                        if bug.confirmed == 1:
                            doing += 1
                        else:
                            wait += 1
        except:
            pass

        finally:
            res = {
                "test": {
                    'total': doing+done+wait,  # 总需求+设计的需求（并集）
                    'doing': doing,
                    'done': done,
                    'wait': wait  # 用总需求-这里涉及到的需求（差集）
                }
            }

        return res


    def implement_stats(self, released_stories, done_tests, test_stories):
        # print(released_stories, done_tests)
        # 进行中,完成测试但还没发布（验收）
        doing = len(done_tests - released_stories)
        # 已完成，完成测试且发布
        done = len(done_tests & released_stories)

        # print(released_stories)
        # print(done_tests)
        # print(self.stories)

        # 总需求+设计的需求（并集）
        total = len(self.stories | test_stories)

        res = {
            "implement": {
                'total': total,
                'doing': doing,
                'done': done,
                'wait': total - doing - done
            }
        }

        return res
